from flask import Blueprint, request, render_template, redirect, url_for, jsonify, session, g, make_response, send_file, \
    Response, send_from_directory
from flask_restful import Api, Resource, reqparse, inputs, fields, marshal, marshal_with

import pymysql
#
# # 连接数据库 autocommit = True自动提交
# db = pymysql.connect(host="127.0.0.1", port=3306, user="root", passwd="12345678", db="flask07", charset="utf8",
#                      autocommit=True)
# # 使用cursor()方法创建一个游标对象,cursor其实是调用了cursors模块下的Cursor的类，这个模块主要的作用就是用来和数据库交互的
# # cursor = db.cursor()
# # #使用execute()方法执行SQL语句
# # cursor.execute("SELECT * FROM user")
# # #使用fetall()获取全部数据
# # data = cursor.fetchall()
# # print(data)
#
# # commit()方法：在数据库里增、删、改的时候，必须要进行提交，否则插入的数据不生效。
#
#
# # 默认情况下，我们获取到的返回值是元组，只能看到每行的数据，却不知道每一列代表的是什么，这个时候可以使用以下方式来返回字典，每一行的数据都会生成一个字典：
# cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
# sql = "SELECT * FROM user"
# cursor.execute(sql)
# res = cursor.fetchall()
# # print(res)
#
#
# # 关闭光标对象
# # cursor.close()
# # 关闭光标对象
# # db.close()
#
# # 在执行 UPDATE/INSERT/DELETE 之类的操作，必须使用 conn.commit() 进行事务提交后方可生效。
#
# def create_table(tbl_name):
#     '''新建数据表'''
#     sql = 'create table if not exists %s(id int PRIMARY KEY NOT NULL,name text)' % tbl_name
#     cursor.execute(sql)
#
#
# # tbl_name = 'testA01'
# # create_table('testA01')
#
#
# def create_key(key_name):
#     sql = "alter table testA01 add %s" % key_name
#     cursor.execute(sql)
#
#
# # key_name = 'key01 text'
# # create_key(key_name)
#
#
# sqldata_bp = Blueprint('sqldata', __name__, url_prefix='/data', template_folder='templates')
# api = Api(sqldata_bp)
#
# sqldata_parser = reqparse.RequestParser()
# sqldata_parser.add_argument('tbl_name', type=str, help='表格名字不对', location=['form', 'args', 'json', 'files'])
# sqldata_parser.add_argument('key_name', type=str, help='字段设置不对', location=['form', 'args', 'json', 'files'])
#
#
#
# #--根据学校名称查询--
# class sqldata(Resource):
#     def post(self):
#         args = sqldata_parser.parse_args()
#
#         key_name = args.get('key_name')
#         create_key(key_name)
#         return 200
#
# api.add_resource(sqldata, '/sqldata')
#
#
#
# # sql = "insert into addfields set id=%s" %i

# 事务
# db = pymysql.connect(host="127.0.0.1", port=3306, user="root", passwd="12345678", db="flask01", charset="utf8",
#                      autocommit=True)
# cursor = db.cursor()
# db.begin()
#
# try:
#     cursor.execute("update xapi_test set xapi_name='展开讨论' where xapi_name='擦黑板' ")
#     cursor.execute("update xapi_test set xapi_name='观看视频' where xapi_name='提出问题' ")
# except Exception as e:
#     print("回滚")
#     db.rollback()
# else:
#     print("提交")
#     db.commit()
# cursor.close()
# db.close()

# 数据库连接池
# import threading
# import pymysql
# from dbutils.pooled_db import PooledDB
#
# MYSQL_DB_POOL = PooledDB(
#     creator=pymysql,
#     maxconnections=50,
#     mincached=2,
#     maxcached=3,
#     blocking=True,
#     setsession=[],
#     ping=0,
#     host='127.0.0.1',
#     port=3306,
#     user='root',
#     password='12345678',
#     database='flask01',
#     charset='utf8'
# )
#
# def task():
#     db = MYSQL_DB_POOL.connection()
#     cursor = db.cursor(pymysql.cursors.DictCursor)
#     cursor.execute('select * from xapi_test')
#     result = cursor.fetchall()
#     print(result)
#
# def run():
#     for i in range(10):
#         t = threading.Thread(target=task)
#         t.start()
#
#
# if __name__ == '__main__':
#     run()


# 数据库连接池
# import pymysql
# from dbutils.pooled_db import PooledDB

# class DBHelper(object):
#     def __init__(self):
#         self.pool = PooledDB(
#             creator=pymysql,
#             maxconnections=5,  # 连接池允许的最大连接数，0和None表示不限制连接数
#             mincached=2,  # 初始化时，连接池中至少创建的空闲的连接，0表示不创建
#             maxcached=3,  # 连接池中最多闲置的连接，0和None不限制
#             blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True,等待;False,不等待然后报错
#             setsession=[],  # 开始会话前执行的命令列表。如:["set datestyle to ...","set time zone ..."]
#             ping=0,
#             host='127.0.0.1',
#             port=3306,
#             user='root',
#             password='12345678',
#             database='flask01',
#             charset='utf8'
#         )
#
#     def get_conn_cursor(self):
#         conn = self.pool.connection()
#         cursor = conn.cursor(pymysql.cursors.DictCursor)
#         return conn,cursor
#
#     def close_conn_cursor(self,*args):
#         for item in args:
#             item.close()
#
#     def exec(self,sql,**kwargs):
#         conn,cursor = self.get_conn_cursor()
#         cursor.execute(sql,kwargs)
#         conn.commit()
#         self.close_conn_cursor(conn,cursor)
#
#     def fetch_one(self,sql,**kwargs):
#         conn,cursor = self.get_conn_cursor()
#         cursor.execute(sql,kwargs)
#         result = cursor.fetchone()
#         self.close_conn_cursor(conn,cursor)
#         return result
#
#     def fetch_all(self,sql,**kwargs):
#         conn,cursor = self.get_conn_cursor()
#         cursor.execute(sql,kwargs)
#         result = cursor.fetchall()
#         self.close_conn_cursor(conn,cursor)
#         return result
#
# db = DBHelper()


# 上下文管理
#
# import threading
# import pymysql
# from dbutils.pooled_db import PooledDB
#
# POOL = PooledDB(
#     creator=pymysql,
#     maxconnections=5,  # 连接池允许的最大连接数，0和None表示不限制连接数
#     mincached=2,  # 初始化时，连接池中至少创建的空闲的连接，0表示不创建
#     maxcached=3,  # 连接池中最多闲置的连接，0和None不限制
#     blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True,等待;False,不等待然后报错
#     setsession=[],  # 开始会话前执行的命令列表。如:["set datestyle to ...","set time zone ..."]
#     ping=0,
#     host='127.0.0.1',
#     port=3306,
#     user='root',
#     password='12345678',
#     database='flask01',
#     charset='utf8'
# )
#
#
# class Connect(object):
#     def __init__(self):
#         self.conn = conn = POOL.connection()
#         self.cursor = conn.cursor(pymysql.cursors.DictCursor)
#
#     def __enter__(self):
#         return self
#
#     def __exit__(self, exc_type, exc_val, exc_tb):
#         self.cursor.close()
#         self.conn.close()
#
#     def exec(self, sql, **kwargs):
#         self.cursor.execute(sql, kwargs)
#         self.conn.commit()
#
#     def fetch_one(self, sql, *args,**kwargs):
#         parms = args or kwargs
#         self.cursor.execute(sql, parms)
#         result = self.cursor.fetchone()
#         return result
#
#     def fetch_all(self, sql,*args, **kwargs):
#         parms = args or kwargs
#         self.cursor.execute(sql, parms)
#         result = self.cursor.fetchall()
#         return result









